{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.1 Introducing the Data Sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8940</td>\n",
       "      <td>The New York City Anime Group</td>\n",
       "      <td>29</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104</td>\n",
       "      <td>NYC Pit Bull Group</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   group_id                           name  category_id  city_id\n",
       "0      6388         Alternative Health NYC           14    10001\n",
       "1      6510      Alternative Energy Meetup            4    10001\n",
       "2      8458              NYC Animal Rights           26    10001\n",
       "3      8940  The New York City Anime Group           29    10001\n",
       "4     10104             NYC Pit Bull Group           26    10001"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups1 = pd.read_csv(\"meetup/groups1.csv\")\n",
    "groups1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18879327</td>\n",
       "      <td>BachataMania</td>\n",
       "      <td>5</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18880221</td>\n",
       "      <td>Photoshoot Chicago - Photography and Modeling ...</td>\n",
       "      <td>27</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18880426</td>\n",
       "      <td>Chicago Adult Push / Kick Scooter Group Riding...</td>\n",
       "      <td>31</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18880495</td>\n",
       "      <td>Chicago International Soccer Club</td>\n",
       "      <td>32</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18880695</td>\n",
       "      <td>Impact.tech San Francisco Meetup</td>\n",
       "      <td>2</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   group_id                                               name  category_id  \\\n",
       "0  18879327                                       BachataMania            5   \n",
       "1  18880221  Photoshoot Chicago - Photography and Modeling ...           27   \n",
       "2  18880426  Chicago Adult Push / Kick Scooter Group Riding...           31   \n",
       "3  18880495                  Chicago International Soccer Club           32   \n",
       "4  18880695                   Impact.tech San Francisco Meetup            2   \n",
       "\n",
       "   city_id  \n",
       "0    10001  \n",
       "1    60601  \n",
       "2    60601  \n",
       "3    60601  \n",
       "4    94101  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups2 = pd.read_csv(\"meetup/groups2.csv\")\n",
    "groups2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_id</th>\n",
       "      <th>category_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Arts &amp; Culture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Cars &amp; Motorcycles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Community &amp; Environment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Dancing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Education &amp; Learning</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   category_id            category_name\n",
       "0            1           Arts & Culture\n",
       "1            3       Cars & Motorcycles\n",
       "2            4  Community & Environment\n",
       "3            5                  Dancing\n",
       "4            6     Education & Learning"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories = pd.read_csv(\"meetup/categories.csv\")\n",
    "categories.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7093</td>\n",
       "      <td>West New York</td>\n",
       "      <td>NJ</td>\n",
       "      <td>7093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13417</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>NY</td>\n",
       "      <td>13417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>46312</td>\n",
       "      <td>East Chicago</td>\n",
       "      <td>IN</td>\n",
       "      <td>46312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>56567</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>MN</td>\n",
       "      <td>56567</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            city state    zip\n",
       "0   7093   West New York    NJ   7093\n",
       "1  10001        New York    NY  10001\n",
       "2  13417  New York Mills    NY  13417\n",
       "3  46312    East Chicago    IN  46312\n",
       "4  56567  New York Mills    MN  56567"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"meetup/cities.csv\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7093</td>\n",
       "      <td>West New York</td>\n",
       "      <td>NJ</td>\n",
       "      <td>07093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13417</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>NY</td>\n",
       "      <td>13417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>46312</td>\n",
       "      <td>East Chicago</td>\n",
       "      <td>IN</td>\n",
       "      <td>46312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>56567</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>MN</td>\n",
       "      <td>56567</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            city state    zip\n",
       "0   7093   West New York    NJ  07093\n",
       "1  10001        New York    NY  10001\n",
       "2  13417  New York Mills    NY  13417\n",
       "3  46312    East Chicago    IN  46312\n",
       "4  56567  New York Mills    MN  56567"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities = pd.read_csv(\n",
    "    \"meetup/cities.csv\", dtype = {\"zip\": \"string\"}\n",
    ")\n",
    "cities.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.2 Concatenating Data Sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8940</td>\n",
       "      <td>The New York City Anime Group</td>\n",
       "      <td>29</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104</td>\n",
       "      <td>NYC Pit Bull Group</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8326</th>\n",
       "      <td>26377464</td>\n",
       "      <td>Shinect</td>\n",
       "      <td>34</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8327</th>\n",
       "      <td>26377698</td>\n",
       "      <td>The art of getting what you want [conference s...</td>\n",
       "      <td>14</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8328</th>\n",
       "      <td>26378067</td>\n",
       "      <td>Streeterville Running Group</td>\n",
       "      <td>9</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8329</th>\n",
       "      <td>26378128</td>\n",
       "      <td>Just Dance NYC</td>\n",
       "      <td>23</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8330</th>\n",
       "      <td>26378470</td>\n",
       "      <td>FREE Arabic Chicago Evanston North Burbs</td>\n",
       "      <td>31</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16330 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      group_id                                               name  \\\n",
       "0         6388                             Alternative Health NYC   \n",
       "1         6510                          Alternative Energy Meetup   \n",
       "2         8458                                  NYC Animal Rights   \n",
       "3         8940                      The New York City Anime Group   \n",
       "4        10104                                 NYC Pit Bull Group   \n",
       "...        ...                                                ...   \n",
       "8326  26377464                                            Shinect   \n",
       "8327  26377698  The art of getting what you want [conference s...   \n",
       "8328  26378067                        Streeterville Running Group   \n",
       "8329  26378128                                     Just Dance NYC   \n",
       "8330  26378470           FREE Arabic Chicago Evanston North Burbs   \n",
       "\n",
       "      category_id  city_id  \n",
       "0              14    10001  \n",
       "1               4    10001  \n",
       "2              26    10001  \n",
       "3              29    10001  \n",
       "4              26    10001  \n",
       "...           ...      ...  \n",
       "8326           34    94101  \n",
       "8327           14    94101  \n",
       "8328            9    60601  \n",
       "8329           23    10001  \n",
       "8330           31    60601  \n",
       "\n",
       "[16330 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs = [groups1, groups2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7999"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(groups1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8331"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(groups2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16330"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(groups1) + len(groups2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8940</td>\n",
       "      <td>The New York City Anime Group</td>\n",
       "      <td>29</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104</td>\n",
       "      <td>NYC Pit Bull Group</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16325</th>\n",
       "      <td>26377464</td>\n",
       "      <td>Shinect</td>\n",
       "      <td>34</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16326</th>\n",
       "      <td>26377698</td>\n",
       "      <td>The art of getting what you want [conference s...</td>\n",
       "      <td>14</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16327</th>\n",
       "      <td>26378067</td>\n",
       "      <td>Streeterville Running Group</td>\n",
       "      <td>9</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16328</th>\n",
       "      <td>26378128</td>\n",
       "      <td>Just Dance NYC</td>\n",
       "      <td>23</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16329</th>\n",
       "      <td>26378470</td>\n",
       "      <td>FREE Arabic Chicago Evanston North Burbs</td>\n",
       "      <td>31</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16330 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       group_id                                               name  \\\n",
       "0          6388                             Alternative Health NYC   \n",
       "1          6510                          Alternative Energy Meetup   \n",
       "2          8458                                  NYC Animal Rights   \n",
       "3          8940                      The New York City Anime Group   \n",
       "4         10104                                 NYC Pit Bull Group   \n",
       "...         ...                                                ...   \n",
       "16325  26377464                                            Shinect   \n",
       "16326  26377698  The art of getting what you want [conference s...   \n",
       "16327  26378067                        Streeterville Running Group   \n",
       "16328  26378128                                     Just Dance NYC   \n",
       "16329  26378470           FREE Arabic Chicago Evanston North Burbs   \n",
       "\n",
       "       category_id  city_id  \n",
       "0               14    10001  \n",
       "1                4    10001  \n",
       "2               26    10001  \n",
       "3               29    10001  \n",
       "4               26    10001  \n",
       "...            ...      ...  \n",
       "16325           34    94101  \n",
       "16326           14    94101  \n",
       "16327            9    60601  \n",
       "16328           23    10001  \n",
       "16329           31    60601  \n",
       "\n",
       "[16330 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs = [groups1, groups2], ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">G1</th>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8940</td>\n",
       "      <td>The New York City Anime Group</td>\n",
       "      <td>29</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104</td>\n",
       "      <td>NYC Pit Bull Group</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">G2</th>\n",
       "      <th>8326</th>\n",
       "      <td>26377464</td>\n",
       "      <td>Shinect</td>\n",
       "      <td>34</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8327</th>\n",
       "      <td>26377698</td>\n",
       "      <td>The art of getting what you want [conference s...</td>\n",
       "      <td>14</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8328</th>\n",
       "      <td>26378067</td>\n",
       "      <td>Streeterville Running Group</td>\n",
       "      <td>9</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8329</th>\n",
       "      <td>26378128</td>\n",
       "      <td>Just Dance NYC</td>\n",
       "      <td>23</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8330</th>\n",
       "      <td>26378470</td>\n",
       "      <td>FREE Arabic Chicago Evanston North Burbs</td>\n",
       "      <td>31</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16330 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         group_id                                               name  \\\n",
       "G1 0         6388                             Alternative Health NYC   \n",
       "   1         6510                          Alternative Energy Meetup   \n",
       "   2         8458                                  NYC Animal Rights   \n",
       "   3         8940                      The New York City Anime Group   \n",
       "   4        10104                                 NYC Pit Bull Group   \n",
       "...           ...                                                ...   \n",
       "G2 8326  26377464                                            Shinect   \n",
       "   8327  26377698  The art of getting what you want [conference s...   \n",
       "   8328  26378067                        Streeterville Running Group   \n",
       "   8329  26378128                                     Just Dance NYC   \n",
       "   8330  26378470           FREE Arabic Chicago Evanston North Burbs   \n",
       "\n",
       "         category_id  city_id  \n",
       "G1 0              14    10001  \n",
       "   1               4    10001  \n",
       "   2              26    10001  \n",
       "   3              29    10001  \n",
       "   4              26    10001  \n",
       "...              ...      ...  \n",
       "G2 8326           34    94101  \n",
       "   8327           14    94101  \n",
       "   8328            9    60601  \n",
       "   8329           23    10001  \n",
       "   8330           31    60601  \n",
       "\n",
       "[16330 rows x 4 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs = [groups1, groups2], keys = [\"G1\", \"G2\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "groups = pd.concat(objs = [groups1, groups2], ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.2.1 Missing Values in Combined DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Football</th>\n",
       "      <th>Baseball</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>New England Patriots</td>\n",
       "      <td>Houston Astros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>Philadelphia Eagles</td>\n",
       "      <td>Boston Red Sox</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Football        Baseball\n",
       "2017  New England Patriots  Houston Astros\n",
       "2018   Philadelphia Eagles  Boston Red Sox"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sports_champions_A = pd.DataFrame(\n",
    "    data = [\n",
    "        [\"New England Patriots\", \"Houston Astros\"],\n",
    "        [\"Philadelphia Eagles\", \"Boston Red Sox\"]\n",
    "    ],\n",
    "    columns = [\"Football\", \"Baseball\"],\n",
    "    index = [2017, 2018]\n",
    ")\n",
    "\n",
    "sports_champions_A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "sports_champions_B = pd.DataFrame(\n",
    "    data = [\n",
    "        [\"New England Patriots\", \"St. Louis Blues\"],\n",
    "        [\"Kansas City Chiefs\", \"Tampa Bay Lightning\"]\n",
    "    ],\n",
    "    columns = [\"Football\", \"Hockey\"],\n",
    "    index = [2019, 2020]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Football</th>\n",
       "      <th>Baseball</th>\n",
       "      <th>Hockey</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>New England Patriots</td>\n",
       "      <td>Houston Astros</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>Philadelphia Eagles</td>\n",
       "      <td>Boston Red Sox</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>New England Patriots</td>\n",
       "      <td>NaN</td>\n",
       "      <td>St. Louis Blues</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>Kansas City Chiefs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Tampa Bay Lightning</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Football        Baseball               Hockey\n",
       "2017  New England Patriots  Houston Astros                  NaN\n",
       "2018   Philadelphia Eagles  Boston Red Sox                  NaN\n",
       "2019  New England Patriots             NaN      St. Louis Blues\n",
       "2020    Kansas City Chiefs             NaN  Tampa Bay Lightning"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs = [sports_champions_A, sports_champions_B])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "sports_champions_C = pd.DataFrame(\n",
    "    data = [\n",
    "        [\"Pittsburgh Penguins\", \"Golden State Warriors\"],\n",
    "        [\"Washington Capitals\", \"Golden State Warriors\"]\n",
    "    ],\n",
    "    columns = [\"Hockey\", \"Basketball\"],\n",
    "    index = [2017, 2018]\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Football</th>\n",
       "      <th>Baseball</th>\n",
       "      <th>Hockey</th>\n",
       "      <th>Basketball</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>New England Patriots</td>\n",
       "      <td>Houston Astros</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>Philadelphia Eagles</td>\n",
       "      <td>Boston Red Sox</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pittsburgh Penguins</td>\n",
       "      <td>Golden State Warriors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Washington Capitals</td>\n",
       "      <td>Golden State Warriors</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Football        Baseball               Hockey  \\\n",
       "2017  New England Patriots  Houston Astros                  NaN   \n",
       "2018   Philadelphia Eagles  Boston Red Sox                  NaN   \n",
       "2017                   NaN             NaN  Pittsburgh Penguins   \n",
       "2018                   NaN             NaN  Washington Capitals   \n",
       "\n",
       "                 Basketball  \n",
       "2017                    NaN  \n",
       "2018                    NaN  \n",
       "2017  Golden State Warriors  \n",
       "2018  Golden State Warriors  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs = [sports_champions_A, sports_champions_C])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Football</th>\n",
       "      <th>Baseball</th>\n",
       "      <th>Hockey</th>\n",
       "      <th>Basketball</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>New England Patriots</td>\n",
       "      <td>Houston Astros</td>\n",
       "      <td>Pittsburgh Penguins</td>\n",
       "      <td>Golden State Warriors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>Philadelphia Eagles</td>\n",
       "      <td>Boston Red Sox</td>\n",
       "      <td>Washington Capitals</td>\n",
       "      <td>Golden State Warriors</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Football        Baseball               Hockey  \\\n",
       "2017  New England Patriots  Houston Astros  Pittsburgh Penguins   \n",
       "2018   Philadelphia Eagles  Boston Red Sox  Washington Capitals   \n",
       "\n",
       "                 Basketball  \n",
       "2017  Golden State Warriors  \n",
       "2018  Golden State Warriors  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(\n",
    "    objs = [sports_champions_A, sports_champions_C], \n",
    "    axis = 1\n",
    ")\n",
    "pd.concat(\n",
    "    objs = [sports_champions_A, sports_champions_C], axis = \"columns\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.3 Left Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   group_id                       name  category_id  city_id\n",
       "0      6388     Alternative Health NYC           14    10001\n",
       "1      6510  Alternative Energy Meetup            4    10001\n",
       "2      8458          NYC Animal Rights           26    10001"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_id</th>\n",
       "      <th>category_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Arts &amp; Culture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Cars &amp; Motorcycles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Community &amp; Environment</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   category_id            category_name\n",
       "0            1           Arts & Culture\n",
       "1            3       Cars & Motorcycles\n",
       "2            4  Community & Environment"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>category_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "      <td>Health &amp; Wellbeing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "      <td>Community &amp; Environment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8940</td>\n",
       "      <td>The New York City Anime Group</td>\n",
       "      <td>29</td>\n",
       "      <td>10001</td>\n",
       "      <td>Sci-Fi &amp; Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104</td>\n",
       "      <td>NYC Pit Bull Group</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   group_id                           name  category_id  city_id  \\\n",
       "0      6388         Alternative Health NYC           14    10001   \n",
       "1      6510      Alternative Energy Meetup            4    10001   \n",
       "2      8458              NYC Animal Rights           26    10001   \n",
       "3      8940  The New York City Anime Group           29    10001   \n",
       "4     10104             NYC Pit Bull Group           26    10001   \n",
       "\n",
       "             category_name  \n",
       "0       Health & Wellbeing  \n",
       "1  Community & Environment  \n",
       "2                      NaN  \n",
       "3         Sci-Fi & Fantasy  \n",
       "4                      NaN  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.merge(categories, how = \"left\", on = \"category_id\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.4 Inner Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   group_id                       name  category_id  city_id\n",
       "0      6388     Alternative Health NYC           14    10001\n",
       "1      6510  Alternative Energy Meetup            4    10001\n",
       "2      8458          NYC Animal Rights           26    10001"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_id</th>\n",
       "      <th>category_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Arts &amp; Culture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Cars &amp; Motorcycles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Community &amp; Environment</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   category_id            category_name\n",
       "0            1           Arts & Culture\n",
       "1            3       Cars & Motorcycles\n",
       "2            4  Community & Environment"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>category_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "      <td>Health &amp; Wellbeing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>54126</td>\n",
       "      <td>Energy Healers NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "      <td>Health &amp; Wellbeing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>67776</td>\n",
       "      <td>Flourishing Life Meetup</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "      <td>Health &amp; Wellbeing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111855</td>\n",
       "      <td>Hypnosis &amp; NLP NYC - Update Your Brain</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "      <td>Health &amp; Wellbeing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>129277</td>\n",
       "      <td>The Live Food Chicago Community</td>\n",
       "      <td>14</td>\n",
       "      <td>60601</td>\n",
       "      <td>Health &amp; Wellbeing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8032</th>\n",
       "      <td>25536270</td>\n",
       "      <td>New York Cuckold Relationships Meetup</td>\n",
       "      <td>17</td>\n",
       "      <td>10001</td>\n",
       "      <td>Lifestyle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8033</th>\n",
       "      <td>25795045</td>\n",
       "      <td>Pagans Paradise NYC - A Haven for Heathens</td>\n",
       "      <td>17</td>\n",
       "      <td>10001</td>\n",
       "      <td>Lifestyle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8034</th>\n",
       "      <td>25856573</td>\n",
       "      <td>Fuck Yeah Femme Productions</td>\n",
       "      <td>17</td>\n",
       "      <td>94101</td>\n",
       "      <td>Lifestyle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8035</th>\n",
       "      <td>26158102</td>\n",
       "      <td>Chicago Crossdresser Meetup</td>\n",
       "      <td>17</td>\n",
       "      <td>60601</td>\n",
       "      <td>Lifestyle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8036</th>\n",
       "      <td>26219043</td>\n",
       "      <td>Corporate Goes Conscious</td>\n",
       "      <td>17</td>\n",
       "      <td>10001</td>\n",
       "      <td>Lifestyle</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8037 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      group_id                                        name  category_id  \\\n",
       "0         6388                      Alternative Health NYC           14   \n",
       "1        54126                          Energy Healers NYC           14   \n",
       "2        67776                     Flourishing Life Meetup           14   \n",
       "3       111855      Hypnosis & NLP NYC - Update Your Brain           14   \n",
       "4       129277             The Live Food Chicago Community           14   \n",
       "...        ...                                         ...          ...   \n",
       "8032  25536270       New York Cuckold Relationships Meetup           17   \n",
       "8033  25795045  Pagans Paradise NYC - A Haven for Heathens           17   \n",
       "8034  25856573                 Fuck Yeah Femme Productions           17   \n",
       "8035  26158102                 Chicago Crossdresser Meetup           17   \n",
       "8036  26219043                    Corporate Goes Conscious           17   \n",
       "\n",
       "      city_id       category_name  \n",
       "0       10001  Health & Wellbeing  \n",
       "1       10001  Health & Wellbeing  \n",
       "2       10001  Health & Wellbeing  \n",
       "3       10001  Health & Wellbeing  \n",
       "4       60601  Health & Wellbeing  \n",
       "...       ...                 ...  \n",
       "8032    10001           Lifestyle  \n",
       "8033    10001           Lifestyle  \n",
       "8034    94101           Lifestyle  \n",
       "8035    60601           Lifestyle  \n",
       "8036    10001           Lifestyle  \n",
       "\n",
       "[8037 rows x 5 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.merge(categories, how = \"inner\", on = \"category_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>54126</td>\n",
       "      <td>Energy Healers NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>67776</td>\n",
       "      <td>Flourishing Life Meetup</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>111855</td>\n",
       "      <td>Hypnosis &amp; NLP NYC - Update Your Brain</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>129277</td>\n",
       "      <td>The Live Food Chicago Community</td>\n",
       "      <td>14</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16174</th>\n",
       "      <td>26291539</td>\n",
       "      <td>The Transformation Project: Collaborative Life...</td>\n",
       "      <td>14</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16201</th>\n",
       "      <td>26299876</td>\n",
       "      <td>Cognitive Empathy, How To Translate Enemy Imag...</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16248</th>\n",
       "      <td>26322976</td>\n",
       "      <td>Contemplative Practices Group</td>\n",
       "      <td>14</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16314</th>\n",
       "      <td>26366221</td>\n",
       "      <td>The art of getting what you want: hacking fear</td>\n",
       "      <td>14</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16326</th>\n",
       "      <td>26377698</td>\n",
       "      <td>The art of getting what you want [conference s...</td>\n",
       "      <td>14</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>870 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       group_id                                               name  \\\n",
       "0          6388                             Alternative Health NYC   \n",
       "52        54126                                 Energy Healers NYC   \n",
       "78        67776                            Flourishing Life Meetup   \n",
       "121      111855             Hypnosis & NLP NYC - Update Your Brain   \n",
       "136      129277                    The Live Food Chicago Community   \n",
       "...         ...                                                ...   \n",
       "16174  26291539  The Transformation Project: Collaborative Life...   \n",
       "16201  26299876  Cognitive Empathy, How To Translate Enemy Imag...   \n",
       "16248  26322976                      Contemplative Practices Group   \n",
       "16314  26366221     The art of getting what you want: hacking fear   \n",
       "16326  26377698  The art of getting what you want [conference s...   \n",
       "\n",
       "       category_id  city_id  \n",
       "0               14    10001  \n",
       "52              14    10001  \n",
       "78              14    10001  \n",
       "121             14    10001  \n",
       "136             14    60601  \n",
       "...            ...      ...  \n",
       "16174           14    94101  \n",
       "16201           14    10001  \n",
       "16248           14    94101  \n",
       "16314           14    94101  \n",
       "16326           14    94101  \n",
       "\n",
       "[870 rows x 4 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups[groups[\"category_id\"] == 14]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_id</th>\n",
       "      <th>category_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14</td>\n",
       "      <td>Health &amp; Wellbeing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   category_id       category_name\n",
       "8           14  Health & Wellbeing"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories[categories[\"category_id\"] == 14]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.5 Outer Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   group_id                       name  category_id  city_id\n",
       "0      6388     Alternative Health NYC           14    10001\n",
       "1      6510  Alternative Energy Meetup            4    10001\n",
       "2      8458          NYC Animal Rights           26    10001"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7093</td>\n",
       "      <td>West New York</td>\n",
       "      <td>NJ</td>\n",
       "      <td>07093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13417</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>NY</td>\n",
       "      <td>13417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            city state    zip\n",
       "0   7093   West New York    NJ  07093\n",
       "1  10001        New York    NY  10001\n",
       "2  13417  New York Mills    NY  13417"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388.0</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510.0</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458.0</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8940.0</td>\n",
       "      <td>The New York City Anime Group</td>\n",
       "      <td>29.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104.0</td>\n",
       "      <td>NYC Pit Bull Group</td>\n",
       "      <td>26.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16329</th>\n",
       "      <td>24303427.0</td>\n",
       "      <td>Midwest FPGA/AI/Machine Learning Meetup</td>\n",
       "      <td>34.0</td>\n",
       "      <td>60064.0</td>\n",
       "      <td>60064</td>\n",
       "      <td>North Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>60064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16330</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13417</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>NY</td>\n",
       "      <td>13417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16331</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46312</td>\n",
       "      <td>East Chicago</td>\n",
       "      <td>IN</td>\n",
       "      <td>46312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16332</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56567</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>MN</td>\n",
       "      <td>56567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16333</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95712</td>\n",
       "      <td>Chicago Park</td>\n",
       "      <td>CA</td>\n",
       "      <td>95712</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16334 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         group_id                                     name  category_id  \\\n",
       "0          6388.0                   Alternative Health NYC         14.0   \n",
       "1          6510.0                Alternative Energy Meetup          4.0   \n",
       "2          8458.0                        NYC Animal Rights         26.0   \n",
       "3          8940.0            The New York City Anime Group         29.0   \n",
       "4         10104.0                       NYC Pit Bull Group         26.0   \n",
       "...           ...                                      ...          ...   \n",
       "16329  24303427.0  Midwest FPGA/AI/Machine Learning Meetup         34.0   \n",
       "16330         NaN                                      NaN          NaN   \n",
       "16331         NaN                                      NaN          NaN   \n",
       "16332         NaN                                      NaN          NaN   \n",
       "16333         NaN                                      NaN          NaN   \n",
       "\n",
       "       city_id     id            city state    zip  \n",
       "0      10001.0  10001        New York    NY  10001  \n",
       "1      10001.0  10001        New York    NY  10001  \n",
       "2      10001.0  10001        New York    NY  10001  \n",
       "3      10001.0  10001        New York    NY  10001  \n",
       "4      10001.0  10001        New York    NY  10001  \n",
       "...        ...    ...             ...   ...    ...  \n",
       "16329  60064.0  60064   North Chicago    IL  60064  \n",
       "16330      NaN  13417  New York Mills    NY  13417  \n",
       "16331      NaN  46312    East Chicago    IN  46312  \n",
       "16332      NaN  56567  New York Mills    MN  56567  \n",
       "16333      NaN  95712    Chicago Park    CA  95712  \n",
       "\n",
       "[16334 rows x 8 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.merge(\n",
    "    cities, how = \"outer\", left_on = \"city_id\", right_on = \"id\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388.0</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510.0</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458.0</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8940.0</td>\n",
       "      <td>The New York City Anime Group</td>\n",
       "      <td>29.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104.0</td>\n",
       "      <td>NYC Pit Bull Group</td>\n",
       "      <td>26.0</td>\n",
       "      <td>10001.0</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16329</th>\n",
       "      <td>24303427.0</td>\n",
       "      <td>Midwest FPGA/AI/Machine Learning Meetup</td>\n",
       "      <td>34.0</td>\n",
       "      <td>60064.0</td>\n",
       "      <td>60064</td>\n",
       "      <td>North Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>60064</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16330</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13417</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>NY</td>\n",
       "      <td>13417</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16331</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46312</td>\n",
       "      <td>East Chicago</td>\n",
       "      <td>IN</td>\n",
       "      <td>46312</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16332</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56567</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>MN</td>\n",
       "      <td>56567</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16333</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95712</td>\n",
       "      <td>Chicago Park</td>\n",
       "      <td>CA</td>\n",
       "      <td>95712</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16334 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         group_id                                     name  category_id  \\\n",
       "0          6388.0                   Alternative Health NYC         14.0   \n",
       "1          6510.0                Alternative Energy Meetup          4.0   \n",
       "2          8458.0                        NYC Animal Rights         26.0   \n",
       "3          8940.0            The New York City Anime Group         29.0   \n",
       "4         10104.0                       NYC Pit Bull Group         26.0   \n",
       "...           ...                                      ...          ...   \n",
       "16329  24303427.0  Midwest FPGA/AI/Machine Learning Meetup         34.0   \n",
       "16330         NaN                                      NaN          NaN   \n",
       "16331         NaN                                      NaN          NaN   \n",
       "16332         NaN                                      NaN          NaN   \n",
       "16333         NaN                                      NaN          NaN   \n",
       "\n",
       "       city_id     id            city state    zip      _merge  \n",
       "0      10001.0  10001        New York    NY  10001        both  \n",
       "1      10001.0  10001        New York    NY  10001        both  \n",
       "2      10001.0  10001        New York    NY  10001        both  \n",
       "3      10001.0  10001        New York    NY  10001        both  \n",
       "4      10001.0  10001        New York    NY  10001        both  \n",
       "...        ...    ...             ...   ...    ...         ...  \n",
       "16329  60064.0  60064   North Chicago    IL  60064        both  \n",
       "16330      NaN  13417  New York Mills    NY  13417  right_only  \n",
       "16331      NaN  46312    East Chicago    IN  46312  right_only  \n",
       "16332      NaN  56567  New York Mills    MN  56567  right_only  \n",
       "16333      NaN  95712    Chicago Park    CA  95712  right_only  \n",
       "\n",
       "[16334 rows x 9 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.merge(\n",
    "    cities,\n",
    "    how = \"outer\",\n",
    "    left_on = \"city_id\",\n",
    "    right_on = \"id\",\n",
    "    indicator = True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16330</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13417</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>NY</td>\n",
       "      <td>13417</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16331</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46312</td>\n",
       "      <td>East Chicago</td>\n",
       "      <td>IN</td>\n",
       "      <td>46312</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16332</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56567</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>MN</td>\n",
       "      <td>56567</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16333</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95712</td>\n",
       "      <td>Chicago Park</td>\n",
       "      <td>CA</td>\n",
       "      <td>95712</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       group_id name  category_id  city_id     id            city state  \\\n",
       "16330       NaN  NaN          NaN      NaN  13417  New York Mills    NY   \n",
       "16331       NaN  NaN          NaN      NaN  46312    East Chicago    IN   \n",
       "16332       NaN  NaN          NaN      NaN  56567  New York Mills    MN   \n",
       "16333       NaN  NaN          NaN      NaN  95712    Chicago Park    CA   \n",
       "\n",
       "         zip      _merge  \n",
       "16330  13417  right_only  \n",
       "16331  46312  right_only  \n",
       "16332  56567  right_only  \n",
       "16333  95712  right_only  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outer_join = groups.merge(\n",
    "    cities,\n",
    "    how = \"outer\",\n",
    "    left_on = \"city_id\",\n",
    "    right_on = \"id\",\n",
    "    indicator = True\n",
    ")\n",
    "\n",
    "in_right_only = outer_join[\"_merge\"] == \"right_only\"\n",
    "\n",
    "outer_join[in_right_only].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.6 Merging on Index Labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7093</td>\n",
       "      <td>West New York</td>\n",
       "      <td>NJ</td>\n",
       "      <td>07093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13417</td>\n",
       "      <td>New York Mills</td>\n",
       "      <td>NY</td>\n",
       "      <td>13417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            city state    zip\n",
       "0   7093   West New York    NJ  07093\n",
       "1  10001        New York    NY  10001\n",
       "2  13417  New York Mills    NY  13417"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = cities.set_index(\"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7093</th>\n",
       "      <td>West New York</td>\n",
       "      <td>NJ</td>\n",
       "      <td>07093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10001</th>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13417</th>\n",
       "      <td>New York Mills</td>\n",
       "      <td>NY</td>\n",
       "      <td>13417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 city state    zip\n",
       "id                                \n",
       "7093    West New York    NJ  07093\n",
       "10001        New York    NY  10001\n",
       "13417  New York Mills    NY  13417"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   group_id                       name  category_id  city_id\n",
       "0      6388     Alternative Health NYC           14    10001\n",
       "1      6510  Alternative Energy Meetup            4    10001\n",
       "2      8458          NYC Animal Rights           26    10001"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category_id</th>\n",
       "      <th>city_id</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6388</td>\n",
       "      <td>Alternative Health NYC</td>\n",
       "      <td>14</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6510</td>\n",
       "      <td>Alternative Energy Meetup</td>\n",
       "      <td>4</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8458</td>\n",
       "      <td>NYC Animal Rights</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8940</td>\n",
       "      <td>The New York City Anime Group</td>\n",
       "      <td>29</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104</td>\n",
       "      <td>NYC Pit Bull Group</td>\n",
       "      <td>26</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16325</th>\n",
       "      <td>26377464</td>\n",
       "      <td>Shinect</td>\n",
       "      <td>34</td>\n",
       "      <td>94101</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>CA</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16326</th>\n",
       "      <td>26377698</td>\n",
       "      <td>The art of getting what you want [conference s...</td>\n",
       "      <td>14</td>\n",
       "      <td>94101</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>CA</td>\n",
       "      <td>94101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16327</th>\n",
       "      <td>26378067</td>\n",
       "      <td>Streeterville Running Group</td>\n",
       "      <td>9</td>\n",
       "      <td>60601</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>60290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16328</th>\n",
       "      <td>26378128</td>\n",
       "      <td>Just Dance NYC</td>\n",
       "      <td>23</td>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16329</th>\n",
       "      <td>26378470</td>\n",
       "      <td>FREE Arabic Chicago Evanston North Burbs</td>\n",
       "      <td>31</td>\n",
       "      <td>60601</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>60290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16330 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       group_id                                               name  \\\n",
       "0          6388                             Alternative Health NYC   \n",
       "1          6510                          Alternative Energy Meetup   \n",
       "2          8458                                  NYC Animal Rights   \n",
       "3          8940                      The New York City Anime Group   \n",
       "4         10104                                 NYC Pit Bull Group   \n",
       "...         ...                                                ...   \n",
       "16325  26377464                                            Shinect   \n",
       "16326  26377698  The art of getting what you want [conference s...   \n",
       "16327  26378067                        Streeterville Running Group   \n",
       "16328  26378128                                     Just Dance NYC   \n",
       "16329  26378470           FREE Arabic Chicago Evanston North Burbs   \n",
       "\n",
       "       category_id  city_id           city state    zip  \n",
       "0               14    10001       New York    NY  10001  \n",
       "1                4    10001       New York    NY  10001  \n",
       "2               26    10001       New York    NY  10001  \n",
       "3               29    10001       New York    NY  10001  \n",
       "4               26    10001       New York    NY  10001  \n",
       "...            ...      ...            ...   ...    ...  \n",
       "16325           34    94101  San Francisco    CA  94101  \n",
       "16326           14    94101  San Francisco    CA  94101  \n",
       "16327            9    60601        Chicago    IL  60290  \n",
       "16328           23    10001       New York    NY  10001  \n",
       "16329           31    60601        Chicago    IL  60290  \n",
       "\n",
       "[16330 rows x 7 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups.merge(\n",
    "    cities,\n",
    "    how = \"left\",\n",
    "    left_on = \"city_id\",\n",
    "    right_index = True\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.7 Coding Challenge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Food ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>537</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>658</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>202</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>155</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer ID  Food ID\n",
       "0          537        9\n",
       "1           97        4\n",
       "2          658        1\n",
       "3          202        2\n",
       "4          155        9"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"restaurant/week_1_sales.csv\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "week1 = pd.read_csv(\"restaurant/week_1_sales.csv\")\n",
    "week2 = pd.read_csv(\"restaurant/week_2_sales.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Company</th>\n",
       "      <th>Occupation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Joseph</td>\n",
       "      <td>Perkins</td>\n",
       "      <td>Male</td>\n",
       "      <td>Dynazzy</td>\n",
       "      <td>Community Outreach Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jennifer</td>\n",
       "      <td>Alvarez</td>\n",
       "      <td>Female</td>\n",
       "      <td>DabZ</td>\n",
       "      <td>Senior Quality Engineer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Roger</td>\n",
       "      <td>Black</td>\n",
       "      <td>Male</td>\n",
       "      <td>Tagfeed</td>\n",
       "      <td>Account Executive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Steven</td>\n",
       "      <td>Evans</td>\n",
       "      <td>Male</td>\n",
       "      <td>Fatz</td>\n",
       "      <td>Registered Nurse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Judy</td>\n",
       "      <td>Morrison</td>\n",
       "      <td>Female</td>\n",
       "      <td>Demivee</td>\n",
       "      <td>Legal Assistant</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   First Name Last Name  Gender  Company                     Occupation\n",
       "ID                                                                     \n",
       "1      Joseph   Perkins    Male  Dynazzy  Community Outreach Specialist\n",
       "2    Jennifer   Alvarez  Female     DabZ        Senior Quality Engineer\n",
       "3       Roger     Black    Male  Tagfeed              Account Executive\n",
       "4      Steven     Evans    Male     Fatz               Registered Nurse\n",
       "5        Judy  Morrison  Female  Demivee                Legal Assistant"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"restaurant/customers.csv\", index_col = \"ID\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers = pd.read_csv(\n",
    "    \"restaurant/customers.csv\", index_col = \"ID\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Food Item</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Food ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sushi</td>\n",
       "      <td>3.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Burrito</td>\n",
       "      <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Taco</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Quesadilla</td>\n",
       "      <td>4.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Pizza</td>\n",
       "      <td>2.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Pasta</td>\n",
       "      <td>13.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Steak</td>\n",
       "      <td>24.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Salad</td>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Donut</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Drink</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Food Item  Price\n",
       "Food ID                   \n",
       "1             Sushi   3.99\n",
       "2           Burrito   9.99\n",
       "3              Taco   2.99\n",
       "4        Quesadilla   4.25\n",
       "5             Pizza   2.49\n",
       "6             Pasta  13.99\n",
       "7             Steak  24.99\n",
       "8             Salad  11.25\n",
       "9             Donut   0.99\n",
       "10            Drink   1.75"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"restaurant/foods.csv\", index_col = \"Food ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "foods = pd.read_csv(\"restaurant/foods.csv\", index_col = \"Food ID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.7.1 Problems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.7.2 Solutions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Food ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Week 1</th>\n",
       "      <th>0</th>\n",
       "      <td>537</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>658</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>202</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>155</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Week 2</th>\n",
       "      <th>245</th>\n",
       "      <td>783</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>556</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>547</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>252</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>249</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Customer ID  Food ID\n",
       "Week 1 0            537        9\n",
       "       1             97        4\n",
       "       2            658        1\n",
       "       3            202        2\n",
       "       4            155        9\n",
       "...                 ...      ...\n",
       "Week 2 245          783       10\n",
       "       246          556       10\n",
       "       247          547        9\n",
       "       248          252        9\n",
       "       249          249        6\n",
       "\n",
       "[500 rows x 2 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(objs = [week1, week2], keys = [\"Week 1\", \"Week 2\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Food ID_x</th>\n",
       "      <th>Food ID_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>537</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>155</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>155</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>503</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>503</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer ID  Food ID_x  Food ID_y\n",
       "0          537          9          5\n",
       "1          155          9          3\n",
       "2          155          1          3\n",
       "3          503          5          8\n",
       "4          503          5          9"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "week1.merge(\n",
    "    right = week2, how = \"inner\", on = \"Customer ID\"\n",
    ").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Food ID_x</th>\n",
       "      <th>Food ID_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>537</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>155</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>503</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>550</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer ID  Food ID_x  Food ID_y\n",
       "0          537          9          5\n",
       "1          155          9          3\n",
       "3          503          5          8\n",
       "5          550          6          7\n",
       "6          101          7          4"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "week1.merge(\n",
    "    right = week2, how = \"inner\", on = \"Customer ID\"\n",
    ").drop_duplicates(subset = [\"Customer ID\"]).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Food ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>304</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>540</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>937</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>233</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>922</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>578</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>578</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer ID  Food ID\n",
       "0          304        3\n",
       "1          540        3\n",
       "2          937       10\n",
       "3          233        3\n",
       "4           21        4\n",
       "5           21        4\n",
       "6          922        1\n",
       "7          578        5\n",
       "8          578        5"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "week1.merge(\n",
    "    right = week2,\n",
    "    how = \"inner\",\n",
    "    on = [\"Customer ID\", \"Food ID\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Food ID_x</th>\n",
       "      <th>Food ID_y</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>537</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>658</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>202</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>155</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer ID  Food ID_x  Food ID_y     _merge\n",
       "0          537        9.0        5.0       both\n",
       "1           97        4.0        NaN  left_only\n",
       "2          658        1.0        NaN  left_only\n",
       "3          202        2.0        NaN  left_only\n",
       "4          155        9.0        3.0       both"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "week1.merge(\n",
    "    right = week2,\n",
    "    how = \"outer\",\n",
    "    on = \"Customer ID\",\n",
    "    indicator = True\n",
    ").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Food ID</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Company</th>\n",
       "      <th>Occupation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>537</td>\n",
       "      <td>9</td>\n",
       "      <td>Cheryl</td>\n",
       "      <td>Carroll</td>\n",
       "      <td>Female</td>\n",
       "      <td>Zoombeat</td>\n",
       "      <td>Registered Nurse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97</td>\n",
       "      <td>4</td>\n",
       "      <td>Amanda</td>\n",
       "      <td>Watkins</td>\n",
       "      <td>Female</td>\n",
       "      <td>Ozu</td>\n",
       "      <td>Account Coordinator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>658</td>\n",
       "      <td>1</td>\n",
       "      <td>Patrick</td>\n",
       "      <td>Webb</td>\n",
       "      <td>Male</td>\n",
       "      <td>Browsebug</td>\n",
       "      <td>Community Outreach Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>202</td>\n",
       "      <td>2</td>\n",
       "      <td>Louis</td>\n",
       "      <td>Campbell</td>\n",
       "      <td>Male</td>\n",
       "      <td>Rhynoodle</td>\n",
       "      <td>Account Representative III</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>155</td>\n",
       "      <td>9</td>\n",
       "      <td>Carolyn</td>\n",
       "      <td>Diaz</td>\n",
       "      <td>Female</td>\n",
       "      <td>Gigazoom</td>\n",
       "      <td>Database Administrator III</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer ID  Food ID First Name Last Name  Gender    Company  \\\n",
       "0          537        9     Cheryl   Carroll  Female   Zoombeat   \n",
       "1           97        4     Amanda   Watkins  Female        Ozu   \n",
       "2          658        1    Patrick      Webb    Male  Browsebug   \n",
       "3          202        2      Louis  Campbell    Male  Rhynoodle   \n",
       "4          155        9    Carolyn      Diaz  Female   Gigazoom   \n",
       "\n",
       "                      Occupation  \n",
       "0               Registered Nurse  \n",
       "1            Account Coordinator  \n",
       "2  Community Outreach Specialist  \n",
       "3     Account Representative III  \n",
       "4     Database Administrator III  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "week1.merge(\n",
    "    right = customers,\n",
    "    how = \"left\",\n",
    "    left_on = \"Customer ID\",\n",
    "    right_index = True\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.8 Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
